hive sql综合训练（二）
1.1 多表查询
1.1.1 表联结
（1）如图1-1所示，查询有两门以上的课程不及格的同学的学号及其平均成绩
① 先找出有两门以上不及格的学生名单，按照学生分组，过滤组内成绩低于60的并进行count，count>=2。
② 接着做出一张表查询学生的平均成绩并和上一个子查询中的学生学号进行连接
hive>
select
    t1.stu_id,
    t1.avg_score
from (
         select
             stu_id,
             sum(if(score < 60,1,0)) flage
         from score_info
         group by stu_id
         having flage >= 2
) t1
join (
    select
        stu_id,
        avg(score) avg_score
    from score_info
    group by stu_id
) t2 on t1.stu_id = t1.stu_id;
结果

图1-1 查询有两门以上的课程不及格的同学的学号及其平均成绩
（2） 如图1-2所示，查询所有学生的学号、姓名、选课数、总成绩
select
    s.stu_id,
    s.stu_name,
    count(sc.course_id) count_course,
    sum(sc.score) sum_score
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id,s.stu_name;
结果

图1-2 查询所有学生的学号、姓名、选课数、总成绩
（3）如图1-3所示，查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s.stu_id,
       s.stu_name,
       avg(sc.score) avg_score
from score_info sc
left join student_info s on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having avg_score > 85
结果

图1-3查询平均成绩大于85的所有学生的学号、姓名和平均成绩
（4） 如图1-4所示，查询学生的选课情况：学号，姓名，课程号，课程名称
select
    s.stu_id,
    s.stu_name,
    c.course_id,
    c.course_name
from score_info sc
join course_info c on sc.course_id = c.course_id
join student_info s on sc.stu_id = s.stu_id;
结果

图1-4查询学生的选课情况：学号，姓名，课程号，课程名称
（5）如图9-5所示，查询出每门课程的及格人数和不及格人数
select
    c.course_id,
    c.course_name,
    t1.`及格人数`,
    t1.`不及格人数`
from course_info c
join (
    select
        course_id,
        sum(if(score >= 60,1,0)) as `及格人数`,
        sum(if(score < 60,1,0)) as `不及格人数`
    from score_info
    group by course_id
    ) t1 on c.course_id = t1.course_id;
结果

图1-5 查询出每门课程的及格人数和不及格人数
（6）如图1-6所示，查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
select
    s.stu_id,
    s.stu_name,
    t1.score,
    t1.course_id,
    c.course_name
from student_info s
join (
    select
        stu_id,
        score,
        course_id
    from score_info
    where score > 80 and course_id = '03'
    ) t1
on s.stu_id = t1.stu_id
join course_info c on c.course_id = t1.course_id;
结果

图1-6 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
1.1.2 多表连接
（1）如图1-7，课程编号为"01"且课程分数小于60，按分数降序排列的学生信息
select
    s.stu_id,
    s.stu_name,
    s.birthday,
    s.sex,
    t1.score
from student_info s
join (
    select
        stu_id,
        course_id,
        score
    from score_info
    where score < 60 and course_id = '01'
    ) t1
on s.stu_id=t1.stu_id
order by t1.score desc;

图1-7课程编号为"01"且课程分数小于60，按分数降序排列的学生信息

（2）如图1-8所示，查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
select
    s.stu_id,
    s.stu_name,
    c.course_name,
    s1.score
from student_info s
join (
    select
        stu_id,
        sum(if(score >= 70,0,1)) flage
    from score_info
    group by stu_id
    having flage =0
    ) t1
on s.stu_id = t1.stu_id
left join score_info s2 on s.stu_id = s1.stu_id
left join course_info c on s1.course_id = c.course_id;
结果如图1-8所示：

图1-8 查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列
（3）如图1-9所示，查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩
select
    sc1.stu_id,
    sc1.course_id,
    sc1.score
from score_info sc1
join score_info sc2 on sc1.stu_id = sc1.stu_id
and sc1.course_id <> sc1.course_id
and sc1.score = sc1.score;

图1-9查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩
（4）如图1-10所示， 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
知识点：多表连接 + 条件
select
    s1.stu_id
from
(
    select
        sc1.stu_id,
        sc1.course_id,
        sc1.score
    from  score_info sc1
    where sc1.course_id ='01'
) s1
join
(
    select
        sc1.stu_id,
        sc1.course_id,
        score
    from score_info sc2
    where sc1.course_id ="02"
) s2
on s1.stu_id=s1.stu_id
where s1.score > s1.score;

图1-10查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
（5）如图1-11查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select
    t1.stu_id as `学号`,
    s.stu_name as `姓名`
from
(    select
        stu_id
    from score_info sc1
    where sc1.course_id='01'
    and stu_id in (
          select
              stu_id
          from score_info sc2
          where sc1.course_id='02'
          ) )t1
join student_info s
on t1.stu_id = s.stu_id;
结果如图1-11所示：

图1-11 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
（6）如图1-12所示，查询学过“李体音”老师所教的所有课的同学的学号、姓名
select
    t1.stu_id,
    si.stu_name
from
(    select
        stu_id
    from score_info si
    where course_id in
    (
        select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'      --李体音教的所有课程
    )
    group by stu_id
    having count(*)=2       --学习所有课程的学生
)t1
left join student_info si
on t1.stu_id=si.stu_id;

图1-12 查询学过“李体音”老师所教的所有课的同学的学号、姓名
（7）如图1-13所示，查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
select
    t1.stu_id,
    si.stu_name
from
(    select
        stu_id
    from score_info si
    where course_id in
    (      select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'
    )
    group by stu_id
)t1
left join student_info si
on t1.stu_id=si.stu_id;
结果

图1-13 查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
（8）如图1-14所示，查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
select
    si.stu_id,
    si.stu_name
from score_info sc
join student_info si
on sc.stu_id = si.stu_id
where sc.course_id in
(
    select
        course_id
    from score_info
    where stu_id='001'    --001的课程
) and sc.stu_id <> '001'  --排除001学生
group by si.stu_id,si.stu_name;
结果如图1-14所示：

图1-14查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
（9）如图1-15所示， 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
    si.stu_name,
    ci.course_name,
    sc.score,
    t1.avg_score
from score_info sc
join student_info si
on sc.stu_id=si.stu_id
join course_info ci
on sc.course_id=ci.course_id
join
(
    select
        stu_id,
        avg(score) avg_score
    from score_info
    group by stu_id
) t1
on sc.stu_id=t1.stu_id
order by t1.avg_score desc;
结果

 图1-15 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩






II